package com.ktwlrj.core.util.xpoi;

import com.alibaba.fastjson.JSONObject;
import com.ktwlrj.core.util.convert.ConvertUtil;
import com.ktwlrj.core.util.xpoi.model.DataSource;
import com.ktwlrj.core.util.xpoi.model.DataSourceField;
import com.ktwlrj.core.util.xpoi.model.ExcelParseData;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.*;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 根据特定Excel导出Excel文档
 *
 * @author zhouxuanhong
 * @date 2019-08-02 12:14
 * @since JDK1.8
 */
public abstract class BaseWorkbook {
    /**
     * 工作表
     */
    Workbook workBook;

    Workbook parseWorkBook;
    /**
     * 文档输出流
     */
    ByteArrayOutputStream outStream;
    /**
     * excel解析数据
     */
    ExcelParseData excelParseData;
    /**
     * 单个数据源
     */
    DataSource dataSource;

    int dataStartRow;
    /**
     * 多个数据源
     */
    List<DataSource> dataSourceList;

    BaseWorkbook() {
        outStream = new ByteArrayOutputStream();
    }


    public ExcelParseData getExcelParseData() {
        return this.excelParseData;
    }

    public ByteArrayOutputStream getOutStream() {
        return outStream;
    }

    /**
     * 打开excel模板
     *
     * @param filePath ${@link String}
     * @author zhouxuanhong
     * @date 2019-08-02 12:15
     */
    public void open(String filePath) throws IOException, InvalidFormatException {
        File file = new File(filePath);
        FileInputStream fis = new FileInputStream(file);
        // 读取excel模板
        workBook = WorkbookFactory.create(fis);
    }

    /**
     * 打开excel模板
     *
     * @param inputStream ${@link InputStream}
     * @author zhouxuanhong
     * @date 2019-08-02 12:15
     */
    public void open(InputStream inputStream) throws IOException, InvalidFormatException {
        // 读取excel模板
        workBook = WorkbookFactory.create(inputStream);
    }

    /**
     * 打开excel模板
     *
     * @param file ${@link File}
     * @author zhouxuanhong
     * @date 2019-08-02 12:15
     */
    public void open(File file) throws IOException, InvalidFormatException {
        // 读取excel模板
        workBook = WorkbookFactory.create(file);
    }

    /**
     * 添加数据源
     *
     * @param inputStream ${@link InputStream}
     * @author zhouxuanhong
     * @date 2019-08-02 12:16
     */
    public void setParseWorkbook(InputStream inputStream) throws IOException, InvalidFormatException {
        parseWorkBook = WorkbookFactory.create(inputStream);
    }

    /**
     * 添加数据源
     *
     * @param dataSource ${@link DataSource}
     * @author zhouxuanhong
     * @date 2019-08-02 12:16
     */
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * 添加数据源
     *
     * @param dataSourceList ${@link List<DataSource>}
     * @author zhouxuanhong
     * @date 2019-08-02 12:16
     */
    public void setDataSource(List<DataSource> dataSourceList) {
        this.dataSourceList = dataSourceList;
    }

    /**
     * 数据处理
     *
     * @author zhouxuanhong
     * @date 2019-08-02 12:16
     */
    public abstract void process() throws IOException;


    /**
     * 写自定义字段试数据
     *
     * @param sheet      ${@link HSSFSheet}
     * @param cdsField   ${@link Map<String, DataSourceField>}
     * @param dataSource ${@link DataSource}
     * @author zhouxuanhong
     * @date 2019-08-02 12:16
     */
    void writeCustomField(Sheet sheet, Map<String, DataSourceField> cdsField, DataSource dataSource) {
        JSONObject customField = dataSource.getFieldMap();
        for (Map.Entry<String, DataSourceField> entry : cdsField.entrySet()) {
            String key = entry.getKey();
            DataSourceField dsf = entry.getValue();

            if (!customField.containsKey(key)) {
                continue;
            }
            // 获取行
            Row row = sheet.getRow(dsf.getRowIdx());
            // 获取列
            Cell cell = row.getCell(dsf.getColIdx());
            setCellValue(cell, customField, key, dsf);
        }
    }


    /**
     * 解析自定义字段数据
     *
     * @param sheet          ${@link HSSFSheet}
     * @param cdsField       ${@link Map<String, DataSourceField>}
     * @param excelParseData ${@link ExcelParseData}
     * @author zhouxuanhong
     * @date 2019-08-02 12:16
     */
    void parseCustomField(Sheet sheet, Map<String, DataSourceField> cdsField, ExcelParseData excelParseData) {
        JSONObject customData = new JSONObject();
        for (Map.Entry<String, DataSourceField> entry : cdsField.entrySet()) {
            String key = entry.getKey();
            DataSourceField dsf = entry.getValue();
            // 获取行
            Row row = sheet.getRow(dsf.getRowIdx());
            // 获取列
            Cell cell = row.getCell(dsf.getColIdx());
            getCellValue(cell, customData, key, dsf);
        }
        if (excelParseData == null) {
            excelParseData = new ExcelParseData();
        }
        excelParseData.setCustomData(customData);
    }


    /**
     * 写数据源字段数据
     *
     * @param sheet      ${@link HSSFSheet} 工作簿
     * @param dsField    ${@link Map<String, DataSourceField>} 工作簿中数据源字段位置信息
     * @param dataSource ${@link DataSource} 数据源
     * @author zhouxuanhong
     * @date 2019-08-02 12:17
     */
    void writeDataSource(Sheet sheet, Map<String, DataSourceField> dsField, DataSource dataSource) {
        List<JSONObject> ds = dataSource.getDsList();
        for (Map.Entry<String, DataSourceField> entry : dsField.entrySet()) {
            String key = entry.getKey();
            DataSourceField sdf = entry.getValue();
            for (int i = 0; i < ds.size(); i++) {
                JSONObject json = ds.get(i);
                json.put("num", (i + 1));
                if (!json.containsKey(key)) {
                    continue;
                }

                // 获取行
                Row row = sheet.getRow(sdf.getRowIdx() + i);
                if (row == null) {
                    row = sheet.createRow(sdf.getRowIdx() + i);
                }

                // 获取列
                Cell cell = row.getCell(sdf.getColIdx());
                if (cell == null) {
                    cell = row.createCell(sdf.getColIdx());
                }
                // 填充单元格数据并设置样式
                setCellValue(cell, json, key, sdf);
                // 处理单元格合并
                if (sdf.isMerge()) {
                    int[] merged = isMerged(sheet, sdf.getRowIdx() + i, sdf.getMergeStart());
                    if (merged[0] == 0) {
                        int len = sdf.getMergeEnd() - sdf.getMergeStart();
                        for (int j = 1; j <= len; j++) {
                            Cell cell2 = row.getCell(sdf.getColIdx() + j);
                            if (cell2 == null) {
                                cell2 = row.createCell(sdf.getColIdx() + j);
                            }
                            cell2.setCellStyle(sdf.getCellStyle());
                        }
                        CellRangeAddress region = new CellRangeAddress(sdf.getRowIdx() + i, sdf.getRowIdx() + i, sdf.getMergeStart() - 1, sdf.getMergeEnd() - 1);
                        sheet.addMergedRegion(region);
                    }
                }
            }
        }
    }


    /**
     * 解析数据源字段数据
     *
     * @param sheet          ${@link HSSFSheet} 工作簿
     * @param dsField        ${@link Map<String, DataSourceField>} 工作簿中数据源字段位置信息
     * @param excelParseData ${@link ExcelParseData} 数据源
     * @param dataStartRow   ${@link Integer} 数据源开始列
     * @author zhouxuanhong
     * @date 2019-08-02 12:17
     */
    void parseDataSource(Sheet sheet, Map<String, DataSourceField> dsField, int dataStartRow, ExcelParseData excelParseData) {
        List<JSONObject> list = new ArrayList<>();
        int lastRowNum = sheet.getLastRowNum();
        System.out.println("----------BaseWorkbook------1---->parseDataSource:" + lastRowNum);
        System.out.println("----------BaseWorkbook------2---->parseDataSource:" + dataStartRow);
        for (int i = dataStartRow; i <= lastRowNum; i++) {
            // 获取行
            JSONObject data = new JSONObject();
            Row row = sheet.getRow(i);
            for (Map.Entry<String, DataSourceField> entry : dsField.entrySet()) {
                String key = entry.getKey();
                DataSourceField dsf = entry.getValue();
                // 获取列
                Cell cell = row.getCell(dsf.getColIdx());
                getCellValue(cell, data, key, dsf);
            }
            list.add(data);
        }
        if (excelParseData == null) {
            excelParseData = new ExcelParseData();
        }
        excelParseData.setList(list);
    }

    /**
     * 获取工作簿
     *
     * @param index ${@link Integer}  工作簿索引
     * @return HSSFSheet ${@link HSSFSheet}
     * @author zhouxuanhong
     * @date 2019-08-02 12:18
     */
    Sheet getSheet(int index) {
        return workBook.getSheetAt(index);
    }

    /**
     * 获取工作簿待解析字段
     *
     * @param index ${@link Integer}  工作簿索引
     * @return HSSFSheet ${@link HSSFSheet}
     * @author zhouxuanhong
     * @date 2019-08-02 12:18
     */
    Sheet getParseSheet(int index) {
        return parseWorkBook.getSheetAt(index);
    }


    /**
     * 提取模板中数据源相关字段位置信息，包括：行、列、单元格样式
     *
     * @param sheet ${@link HSSFSheet} 工作簿
     * @param map1  ${@link Map<String, DataSourceField>} 自定义字段集
     * @param map2  ${@link Map<String, DataSourceField>} 数据源字段集
     * @author zhouxuanhong
     * @date 2019-08-02 12:18
     */
    void getDataSourceField(Sheet sheet, Map<String, DataSourceField> map1, Map<String, DataSourceField> map2) {
        int rowNum = sheet.getLastRowNum();
        // 提取模板中数据源相关字段位置信息，包括：行、列、单元格样式
        for (int rowIdx = 0; rowIdx <= rowNum; rowIdx++) {
            Row row = sheet.getRow(rowIdx);
            if (row == null) {
                continue;
            }
            // 获得总列数
            int columnNum = row.getPhysicalNumberOfCells();
            for (int colIdx = 0; colIdx < columnNum; colIdx++) {
                // 获取单元格
                Cell cell = row.getCell(colIdx);
                if (cell == null) {
                    continue;
                }

                // 单元格值
                String cellValue = cell.getStringCellValue().trim();

                // 单元格内容不满足模板要求
                if (cellValue.length() == 0 || !cellValue.contains("&=")) {
                    continue;
                }

                DataSourceField dsf = new DataSourceField(rowIdx, colIdx, cell.getCellStyle());
                int length;
                if (cellValue.contains("&=$")) {
                    // 自定义数据源字段
                    String[] split = cellValue.split("&=\\$");
                    length = split.length;
                    String field = split[length - 1];
                    dsf.setValueType(length != 1 ? split[0] : "string");
                    dsf.setFormat(length == 3 ? split[1] : "");
                    map1.put(field, dsf);
                } else if (cellValue.contains("&=")) {
                    // 数据源字段
                    int[] merged = isMerged(sheet, rowIdx, colIdx);
                    if (merged[0] == 1) {
                        dsf.setMerge(true);
                        dsf.setMergeStart(merged[1]);
                        dsf.setMergeEnd(merged[2]);
                    }
                    this.dataStartRow = rowIdx;
                    String[] split = cellValue.split("&=");
                    length = split.length;
                    String field = split[length - 1];
                    dsf.setValueType(length != 1 ? split[0] : "string");
                    dsf.setFormat(length == 3 ? split[1] : "");
                    map2.put(field, dsf);
                }
            }
        }
    }


    /**
     * map list 转 JSONObject
     *
     * @param list ${@link List}
     * @return List<JSONObject> ${@link List<JSONObject>}
     * @author zhouxuanhong
     * @date 2019-08-02 12:19
     */
    public <E> List<JSONObject> listMapToJSONObject(List<E> list) {
        List<JSONObject> dsList = new ArrayList<>(list.size());
        for (Object object : list) {
            Map<String, Object> map = ConvertUtil.map(object, HashMap.class);
            JSONObject jsonObj = new JSONObject();
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                jsonObj.put(entry.getKey(), entry.getValue());
            }
            dsList.add(jsonObj);
        }
        return dsList;
    }


    /**
     * 保存文件
     *
     * @param filePath ${@link String} 目标文件路径
     * @author zhouxuanhong
     * @date 2019-08-02 12:19
     */
    public void saveFile(String filePath) {
        // 获取流字节
        byte[] content = outStream.toByteArray();

        FileOutputStream outputStream = null;
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            // 将流字节转换成输入流
            InputStream is = new ByteArrayInputStream(content);

            // 获取目标文件输出流
            outputStream = new FileOutputStream(filePath);

            // 获取缓冲字节输入流
            bis = new BufferedInputStream(is);

            // 获取缓冲字节输出流
            bos = new BufferedOutputStream(outputStream);

            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (bis != null) {
                    bis.close();
                }

                if (bos != null) {
                    bos.close();
                }

                if (outputStream != null) {
                    outputStream.flush();
                    outputStream.close();
                }
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 判断当前单元格是否合并
     *
     * @param sheet  ${@link Sheet}
     * @param row    ${@link Integer}
     * @param column ${@link Integer}
     * @author zhouxuanhong
     * @date 2019-08-05 00:54
     */
    private int[] isMerged(Sheet sheet, int row, int column) {
        int[] result = new int[3];
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    result[0] = 1;
                    result[1] = firstColumn + 1;
                    result[2] = lastColumn + 1;
                    return result;
                }
            }
        }
        return result;
    }

    /**
     * cell数据填充
     *
     * @param cell ${@link Cell}
     * @param json ${@link JSONObject}
     * @param dsf  ${@link DataSourceField}
     * @author zhouxuanhong
     * @date 2019-08-05 01:34
     */
    private void setCellValue(Cell cell, JSONObject json, String key, DataSourceField dsf) {
        String type = dsf.getValueType();
        String format = dsf.getFormat();
        CellStyle cellStyle = dsf.getCellStyle();
        if (StringUtils.isNotBlank(format)) {
            CreationHelper creationHelper = this.workBook.getCreationHelper();
            short shortFormat = creationHelper.createDataFormat().getFormat(format);
            cellStyle.setDataFormat(shortFormat);
        }
        cellStyle.setWrapText(true);
        cell.setCellStyle(cellStyle);
        switch (type) {
            case "double":
            case "flout":
            case "bigdecimal":
                cell.setCellValue(json.getDouble(key));
                break;
            case "boolean":
                cell.setCellValue(json.getBoolean(key));
                break;
            case "int":
            case "integer":
                cell.setCellValue(json.getInteger(key));
                break;
            case "date":
                cell.setCellValue(json.getDate(key));
                break;
            default:
                cell.setCellValue(json.getString(key));
        }
    }


    /**
     * <p>
     * cell数据获取
     * valueType--为目标类型
     * format--excel转String和String再转目标类型格式化格式信息
     * </p>
     *
     * @param cell             ${@link Cell}
     * @param targetJsonObject ${@link JSONObject}
     * @param dsf              ${@link DataSourceField}
     * @author zhouxuanhong
     * @date 2019-08-05 01:34
     */
    private void getCellValue(Cell cell, JSONObject targetJsonObject, String key, DataSourceField dsf) {
        if (cell == null || StringUtils.isBlank(key) || dsf == null || targetJsonObject == null) {
            return;
        }
        String format = dsf.getFormat();
        // 处理excel数据读取
        String cellValue = getCellValue(cell, format);
        // 获取目标类型信息
        String valueType = dsf.getValueType();
        if (StringUtils.isBlank(valueType)) {
            valueType = "string";
        }
        // 处理excel数据转换到目标类型
        cellValueToBeanValue(targetJsonObject, cellValue, valueType, format, key);
    }


    /**
     * 读取Cell中数据,并返回(全部转为String类型)
     *
     * @param cell ${@link Cell}
     * @return String ${@link String}
     * @author zhouxuanhong
     * @date 2019-08-09 14:14
     */
    private String getCellValue(Cell cell, String format) {
        String cellValue = "";
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case NUMERIC:
                // 处理日期格式、时间格式
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat(format);
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                    //处理数值格式
                } else if (cell.getCellStyle().getDataFormat() == 0) {
                    cell.setCellType(CellType.STRING);
                    cellValue = String.valueOf(cell.getRichStringCellValue().getString());
                }
                break;
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK:
            case ERROR:
            default:
                cellValue = null;
        }
        return cellValue;
    }


    /**
     * 处理cell value to bean type value
     *
     * @param targetJsonObject ${@link JSONObject} 目标json
     * @param cellValue        ${@link String} cell value
     * @param targetType       ${@link String} 目标类型
     * @param targetFormat     ${@link String} 转为目标类型的格式化format,特别是时间
     * @param targetJsonKey    ${@link String} 目标存放json key
     * @author zhouxuanhong
     * @date 2019-08-09 14:19
     */
    private void cellValueToBeanValue(JSONObject targetJsonObject, String cellValue, String targetType, String targetFormat, String targetJsonKey) {
        switch (targetType) {
            case "double":
                if (StringUtils.isBlank(cellValue)) {
                    cellValue = "0.0";
                }
                targetJsonObject.put(targetJsonKey, Double.valueOf(cellValue));
                break;
            case "flout":
                if (StringUtils.isBlank(cellValue)) {
                    cellValue = "0.0";
                }
                targetJsonObject.put(targetJsonKey, Float.valueOf(cellValue));
                break;
            case "bigdecimal":
                if (StringUtils.isBlank(cellValue)) {
                    cellValue = "0.0";
                }
                targetJsonObject.put(targetJsonKey, new BigDecimal(cellValue));
                break;
            case "boolean":
                if (StringUtils.isBlank(cellValue)) {
                    cellValue = "0";
                }
                targetJsonObject.put(targetJsonKey, Boolean.valueOf(cellValue));
                break;
            case "int":
            case "integer":
                if (StringUtils.isBlank(cellValue)) {
                    cellValue = "0";
                }
                targetJsonObject.put(targetJsonKey, Integer.valueOf(cellValue));
                break;
            case "date":
                if (StringUtils.isBlank(cellValue)) {
                    targetJsonObject.put(targetJsonKey, null);
                } else {
                    SimpleDateFormat sdf = new SimpleDateFormat(targetFormat);
                    System.out.println("----------BaseWorkbook---------->cellValueToBeanValue:" + cellValue);
                    try {
                        Date parse = sdf.parse(cellValue);
                        targetJsonObject.put(targetJsonKey, parse);
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                }
                break;
            case "byte":
                if (StringUtils.isBlank(cellValue)) {
                    cellValue = "0";
                }
                targetJsonObject.put(targetJsonKey, Byte.valueOf(cellValue));
                break;
            case "short":
                if (StringUtils.isBlank(cellValue)) {
                    cellValue = "0";
                }
                targetJsonObject.put(targetJsonKey, Short.valueOf(cellValue));
                break;
            case "char":
                if (StringUtils.isBlank(cellValue)) {
                    cellValue = "0";
                }
                targetJsonObject.put(targetJsonKey, cellValue);
                break;
            default:
                targetJsonObject.put(targetJsonKey, cellValue);
        }
    }
}
